package com.zhq.blog.core.domain.game.support;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.zhq.blog.core.domain.BaseRepository;
import com.zhq.blog.core.domain.game.support.AutoBetBase.AutoBetType;
import com.zhq.blog.core.domain.game.support.Cathectic.MunualOrAuto;

/**
 * @title:CathecticRepository.java
 * @package com.zhq.blog.core.domain.game.support
 * @author 张恒强
 * @date 2014年12月26日 下午2:44:05
 * @version V1.0
 */
public interface CathecticRepository extends JpaRepository<Cathectic, Long>,
		BaseRepository<Cathectic>,JpaSpecificationExecutor<Cathectic>{
	/**
	 * 系统启动后使用，查找所有之前未开奖的期数
	 * @param gameId
	 * @param gamePeriod
	 * @return
	 */
	@Query("select c from Cathectic c where deleted = 0 and c.gameId = :gameId and c.gamePeriod = :gp and c.isLottery = 0")
	List<Cathectic> find_all_not_lottery_and_give_back_bet(@Param("gameId") long gameId,@Param("gp") long gamePeriod);
	/**
	 * 每一期的所有投注 按投注号分组
	 * 得到投注号、该号总投注、该号投注人数
	 * @param gamePeriod
	 * @param gameId
	 * @return
	 */
	@Query("select c.betNumber , sum(c.betMoney),count(c.id) from Cathectic c where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId and c.isLottery = 0 group by c.betNumber order by c.betNumber asc")
	List<Object[]> find_all_by_game_and_period_grou_by_betnumber_asc(@Param("gp") long gamePeriod,@Param("gameId") long gameId);
	/**
	 * 获取中奖号码的投注人数
	 * @param gamePeriod
	 * @param gameId
	 * @param betnumber
	 * @return
	 */
	@Query("select count(distinct c.userId) from Cathectic c where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId and c.isLottery = 0 and c.betNumber = :betnumber")
	int count_by_game_and_period_and_betnumber(@Param("gp") long gamePeriod,@Param("gameId") long gameId,@Param("betnumber") int betnumber);
	
	@Query("select c.betMoney,c.winningMoney from Cathectic c where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId and c.userId = :userId and c.betNumber = :betNumber ")
	List<Object[]> find_bet_money_by_game_period_user_bet_number(@Param("gp") long period,@Param("gameId") long gameId,@Param("userId") long userId,@Param("betNumber") int betNumber);
	
	/**
	 * 获取某游戏某期的总投注
	 * @param gamePeriod
	 * @param gameId
	 * @return
	 */
	@Query(value="select sum(c.betMoney) from Cathectic c where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId")
	Object sum_by_game_and_period(@Param("gp") long gamePeriod,@Param("gameId") long gameId);
	/**
	 * 获取某游戏某期的某号码的总投注
	 * @param gamePeriod
	 * @param gameId
	 * @param betnumber
	 * @return
	 */
	@Query(value="select sum(c.betMoney) from Cathectic c where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId and c.betNumber = :betnumber")
	Object sum_by_game_and_period_and_betnumber(@Param("gp") long gamePeriod,@Param("gameId") long gameId,@Param("betnumber") int betnumber);
	/**
	 * 获取某游戏某期某号码的所有投注列表
	 * @param gamePeriod
	 * @param gameId
	 * @param betnumber
	 * @return
	 */
	@Query("select c from Cathectic c where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId and c.betNumber = :betnumber")
	List<Cathectic> find_all_by_game_and_period_and_betnumber(@Param("gp") long gamePeriod,@Param("gameId") long gameId,@Param("betnumber") int betnumber);
	/**
	 * 修改某游戏某期未中奖号码的投注为已开奖  默认未中奖，所以不需要改
	 * @param gamePeriod
	 * @param gameId
	 * @param betnumber
	 * @return
	 */
	@Modifying
	@Transactional
	@Query("update Cathectic c set c.isLottery = 1 where c.deleted = 0 and c.gamePeriod = :gp and c.gameId = :gameId and c.isLottery = 0 and c.betNumber != :betnumber")
	void update_set_lose_by_game_and_period_and_betnumber(@Param("gp") long gamePeriod,@Param("gameId") long gameId,@Param("betnumber") int betnumber);
	
	@Query("select distinct(c.autoBetId) from Cathectic c where c.deleted = 0 and c.gameId = :gameId and c.gamePeriod = :gp and c.betType = :betType and c.isLottery = 1 and c.munualOrAuto = :moa")
	List<Long> find_all_autoBetId_by_game_period(@Param("gp") long gamePeriod,@Param("gameId") long gameId,@Param("moa") MunualOrAuto moa,@Param("betType") AutoBetType type);
	
	@Query("select c.id from Cathectic c where c.deleted = 0 and c.isLottery = 0 and c.gameId = :gameId and c.gamePeriod = :gp and c.userId = :userId and c.munualOrAuto = :moa")
	List<Long> find_all_by_gameId_period_userId(@Param("gp") long gamePeriod,@Param("gameId") long gameId,@Param("userId") long userId,@Param("moa") MunualOrAuto moa);
	
	@Query("select c from Cathectic c where c.deleted = 0 and c.isLottery = 1 and c.gameId = :gameId and c.userId = :userId and c.createdDate >= :today")
	List<Cathectic> find_all_today_by_gameId_userId_lottered(@Param("gameId") long gameId,@Param("userId") long userId,@Param("today") String today);
	
	
	/**
	 * 获取某玩家在某游戏某期中的投注总钱数
	 * @return
	 */
	@Query("select sum(c.betMoney) from Cathectic c where c.gameId = :gameId and c.gamePeriod = :gp and c.userId = :userId")
	Long sum_by_game_period_user(@Param("gameId") long gameId,@Param("userId") long userId,@Param("gp")long period);
	
	/**
	 * 
	 */
	
	@Query("select sum(c.betMoney) from Cathectic c where c.isWinning =1 and c.gameId = :gameId and c.gamePeriod = :gp and c.userId = :userId")
	Long sum_winning_by_game_period_user(@Param("gameId") long gameId,@Param("userId") long userId,@Param("gp")long period);
	
	/**
	 * 某玩家在某游戏中今天的盈利
	 * @return
	 */
	@Query("select sum(c.winningMoney-c.betMoney) from Cathectic c where c.deleted = 0 and c.isLottery=1 and c.gameId = :gameId and c.userId = :userId and c.createdDate >= :today")
	Long gain_today_by_gameId_userId_lottered(@Param("gameId") long gameId,@Param("userId") long userId,@Param("today") Date today);
	
	/**
	 * 某玩家在某游戏中今天参与的期数 包括自动和手工
	 * @return
	 */
	@Query("select count(distinct c.gamePeriod) from Cathectic c where c.deleted = 0 and c.isLottery=1 and c.gameId = :gameId and c.userId = :userId and c.createdDate >= :today")
	Long numbers_today_by_gameId_userId_lottered(@Param("gameId") long gameId,@Param("userId") long userId,@Param("today") Date today);
	
	/**
	 * 某玩家在某游戏中今天赢的次数 包括自动和手工
	 * @return
	 */
	@Query("select count(distinct c.gamePeriod) from Cathectic c where c.deleted = 0 and c.isLottery=1 and c.isWinning =1 and c.gameId = :gameId and c.userId = :userId and c.createdDate >= :today")
	Long winner_numbers_by_gameId_userId_lottered_winning(@Param("gameId") long gameId,@Param("userId") long userId,@Param("today") Date today);
	
	/**
	 * 查询某玩家在某游戏某天的赢利
	 * @return
	 */
	@Query("select sum(c.winningMoney-c.betMoney) from Cathectic c where c.deleted = 0 and c.isLottery=1 and c.gameId = :gameId and c.userId = :userId and c.createdDate >= :start and c.createdDate <= :end")
	Long sum_by_game_user_someday(@Param("gameId") long gameId,@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);
	
	/**
	 * 查询某玩家某天所有游戏的赢利
	 */
	@Query("select sum(c.winningMoney-c.betMoney) from Cathectic c where c.deleted = 0 and c.isLottery=1 and c.userId = :userId and c.createdDate >= :start and c.createdDate <= :end")
	Long sum_by_user_someday(@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);
	
	/**
	 * 查询某玩家某天在某游戏中总投注期数
	 */
	
	@Query("select count(distinct c.gamePeriod) from Cathectic c where c.deleted = 0 and c.isLottery = 1 and c.gameId = :gameId and c.userId =:userId and c.createdDate >= :start and c.createdDate <= :end")
	Long count_period_by_game_user_someday(@Param("gameId") long gameId,@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);
	
	/**
	 * 查询某玩家某天在某游戏中赢的期数
	 */
	@Query("select count(distinct c.gamePeriod) from Cathectic c where c.isWinning = 1 and c.deleted = 0 and c.isLottery = 1 and c.gameId = :gameId and c.userId =:userId and c.createdDate >= :start and c.createdDate <= :end")
	Long count_win_period_by_game_user_someday(@Param("gameId") long gameId,@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);

	/**
	 * 查询某玩家某天所有游戏中的投注期数
	 */
	@Query(value="select count(distinct c.game_period ,c.game_id) from cathectic c where c.deleted = 0 and c.is_lottery = 1 and c.user_id =:userId and c.created_date >= :start and c.created_date <= :end",nativeQuery=true)
	Long count_period_by_user_someday(@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);
	
	/**
	 * 查询某玩家某天所有游戏中的赢的期数
	 */
	@Query(value="select count(distinct c.game_period ,c.game_id) from cathectic c where c.is_winning = 1 and c.deleted = 0 and c.is_lottery = 1 and c.user_id =:userId and c.created_date >= :start and c.created_date <= :end",nativeQuery=true)
	Long count_win_period_by_user_someday(@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);
	
	
	
	
	/**
	 * 查询某玩家某天在某游戏中参与的所有期数
	 */
	@Query("select distinct c.gamePeriod from Cathectic c where c.deleted =0 and c.isLottery = 1 and c.gameId = :gameId and c.userId =:userId and c.createdDate >= :start and c.createdDate <= :end")
	List<Long> find_period_by_game_user_someday(@Param("gameId") long gameId,@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end);
	
	/**
	 * 查询某玩家某天在某游戏中所有手工或者挂机参与的期数
	 */
	@Query("select distinct c.gamePeriod from Cathectic c where c.deleted =0 and c.isLottery = 1 and c.gameId = :gameId and c.userId =:userId and c.munualOrAuto = :moa and c.createdDate >= :start and c.createdDate <= :end")
	List<Long> find_period_by_game_user_moa_someday(@Param("gameId") long gameId,@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end,@Param("moa") MunualOrAuto moa);
	
	/**
	 * 查询某玩家某天在某游戏中某个挂机类型下的某个自动投注参与的期数
	 */
	@Query("select distinct c.gamePeriod from Cathectic c where c.deleted =0 and c.isLottery = 1 and c.gameId = :gameId and c.userId =:userId and c.munualOrAuto = 1 and c.betType = :bettype and c.autoBetId = :betid and c.createdDate >= :start and c.createdDate <= :end")
	List<Long> find_period_by_game_user_betType_betId_someday(@Param("gameId") long gameId,@Param("userId") long userId,@Param("start") Date start,@Param("end") Date end,@Param("bettype") AutoBetType bettype,@Param("betid") long betId);
	
}
